7-b: SQLite
MyRentSQLite (SQLite) 01 02 03 04 05 06 07 08 09 10 Exercises Appendix-A Appendix-B

Using SQLite in Android Development

Working with a subset of the MyRent app, we develop a module to persist data using SQLite, use the Android Debug Bridge (adb) commandline tool to view the database content and as an exercise replace the file system in MyRent with an SQLite implementation.

Baseline app

Create a baseline app named MyRentSQLite and complete the wizard screesn, following the steps shown in the following figures 1 to 4.

On completing the wizard, the app opens within Android Studio. This is shown in Figures 6 and 6.

Figure 1: Figure 2: Figure 3: Figure 4:

Figure 5: MyRent activity Figure 6: activity-myrent.xml design view

Replace build.gradle (Module:app) with the following version to align with the Android configuration we have been using in this course:

apply plugin: 'com.android.application'

android {
  compileSdkVersion 23
  buildToolsVersion "23.0.3"

  defaultConfig {
    applicationId "sqlite.myrentsqlite"
    minSdkVersion 19
    targetSdkVersion 23
    versionCode 1
    versionName "1.0"
  }
  buildTypes {
    release {
      minifyEnabled false
      proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.pro'
    }
  }
}

dependencies {
  compile fileTree(dir: 'libs', include: ['*.jar'])
  testCompile 'junit:junit:4.12'
  compile 'com.android.support:appcompat-v7:23.4.0'
}

Build and install apk on a device or emulator to display the standard hello world screen as shown in Figure 6.

Project structure

To closely mimic the MyRent app structure, create three packages, activities, app and models as shown in the following pictures:

Figure 1 Figure 2 Figure 3

Move MyRent.java into the activities package. Unfortunately there is a bug in Android Studio 2.1.1 that cause the IDE to freeze if one attempts to drag and drop. This appears to have been resolved in version 2.1.3 but if not then simply copy and paste the file into activities and delete it from its original location.

We shall progressively implement the following features:

We begin with a restricted version of the Residence model class. It contains only 2 fields.

  1. Add a Residence object to the database.
  2. Delete a single Residence object from the database.
  3. Delete all Residence objects in the database.
  4. Obtain a list of all Residence objects in the database.
  5. Obtain a single nominated Residence object from the database.
  6. Add all remaining fields to the Residence model and test previously added features.
  7. Implement an update feature whereby an existing Residence object in the database may have some or all of its fields modified, excluding the primary key.

We will then have implemented Create, Read, Update and Delete (CRUD) capabilities.

Note: not all import statements are included in the code provided. Code completion hints are generally provided that simplifies this task.

Add Residence

We will build the app incrementally, beginning with the adding of a single Residence object to the database.

The refactoring comprises:

  • Adding a button to the layout which when clicked causes a Residence object to be added to the database.
  • Adding an event handler and other code to the activity MyRent to respond to a button click.
  • Subclassing the Android Application class with a new class MyRentApp, located in the app package.
  • Making changes to the AndroidManifest file to reflect the subclassing of the Application class.
  • Subclassing the SQLiteOpenHelper class with a new class DbHelper, located in the app package.
  • Adding a Residence model class that contains, for now, just two fields - the primary key and another.

The project structure, following refactoring, is depicted in Figure 1.

Figure 1: Refactored project structure

Manifest file

Here is the AndroidManifest.xml text. We have added the application name as an attribute (.app.MyRentApp).

<?xml version="1.0" encoding="utf-8"?>
<manifest package="sqlite.myrentsqlite"
          xmlns:android="http://schemas.android.com/apk/res/android">

  <application
      android:name=".app.MyRentApp"
      android:allowBackup="true"
      android:icon="@mipmap/ic_launcher"
      android:label="@string/app_name"
      android:supportsRtl="true"
      android:theme="@style/AppTheme">
    <activity android:name=".activities.MyRent">
      <intent-filter>
        <action android:name="android.intent.action.MAIN"/>

        <category android:name="android.intent.category.LAUNCHER"/>
      </intent-filter>
    </activity>
  </application>

</manifest>

Layout

The refactored layout file activity_myrent.xml , both text and design views follow:

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="sqlite.myrentsqlite.activities.MyRent">

  <Button
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:text="Add Residence"
      android:id="@+id/addResidence"
      android:layout_alignParentLeft="true"
      android:layout_alignParentStart="true"/>

</RelativeLayout>

Figure 2: Design view - MyRentSQLite

Model

package sqlite.myrentsqlite.models;

import java.util.UUID;

public class Residence
{
  public UUID id;
  public String geolocation;


  public Residence()
  {
    id = UUID.randomUUID();
    geolocation = "52.253456,-7.187162";
  }

}

Application subclass

package sqlite.myrentsqlite.app;

import android.app.Application;
import android.util.Log;

public class MyRentApp extends Application
{
  static final String TAG = "MyRentApp";
  public DbHelper dbHelper = null;

  private static MyRentApp app;
  @Override
  public void onCreate()
  {
    super.onCreate();
    dbHelper = new DbHelper(getApplicationContext());
    Log.d(TAG, "MyRent app launched");
    app = this;
  }

  public static MyRentApp getApp(){
    return app;
  }
}

SQLiteOpenHelper subclass

package sqlite.myrentsqlite.app;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import sqlite.myrentsqlite.models.Residence;

public class DbHelper extends SQLiteOpenHelper
{
  static final String TAG = "DbHelper";
  static final String DATABASE_NAME = "residences.db";
  static final int DATABASE_VERSION = 1;
  static final String TABLE_RESIDENCES = "tableResidences";

  static final String PRIMARY_KEY = "id";
  static final String GEOLOCATION = "geolocation";

  Context context;

  public DbHelper(Context context)
  {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    this.context = context;
  }

  @Override
  public void onCreate(SQLiteDatabase db)
  {
    String createTable =
        "CREATE TABLE tableResidences " +
        "(id text primary key, " +
        "geolocation text)";

    db.execSQL(createTable);
    Log.d(TAG, "DbHelper.onCreated: " + createTable);
  }

  /**
   * @param residence Reference to Residence object to be added to database
   */
  public void addResidence(Residence residence)
  {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(PRIMARY_KEY, residence.id.toString());
    values.put(GEOLOCATION, residence.geolocation);
    // Insert record
    db.insert(TABLE_RESIDENCES, null, values);
    db.close();
  }


  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
  {
    db.execSQL("drop table if exists " + TABLE_RESIDENCES);
    Log.d(TAG, "onUpdated");
    onCreate(db);
  }
}

Activity

package sqlite.myrentsqlite.activities;

import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;

import sqlite.myrentsqlite.R;
import sqlite.myrentsqlite.app.MyRentApp;
import sqlite.myrentsqlite.models.Residence;

public class MyRent extends AppCompatActivity implements View.OnClickListener
{

  private Button addResidence;

  MyRentApp app;
  Residence residence;

  @Override
  protected void onCreate(Bundle savedInstanceState)
  {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_myrent);

    app = MyRentApp.getApp();

    addResidence = (Button) findViewById(R.id.addResidence);
    addResidence.setOnClickListener(this);
  }

  @Override
  public void onClick(View v)
  {
    switch (v.getId())
    {
      case R.id.addResidence:
        addResidence();
        break;

    }
  }

  private void addResidence()
  {
    residence = new Residence();

    app.dbHelper.addResidence(residence);

  }
}

Test using adb

To check that the record is correctly inserted into the database we shall use the Android Debug Bridge (adb) commandline tool. You should refer to the Appendix for instructions in the configuration and use of adb.

Run MyRentSQLite app and add a residence by clicking on the Add Residence button.

Switch to a terminal and execute the command:

adb shell

The response should be:

root@generic_x86_64:/ #

Change directory as follows:

cd data/data

Check for the presence of our app:

ls -l
drwxr-x--x u0_a80   u0_a80  2016-07-15 10:57 sqlite.myrentsqlite

If, in Windows, you are unable to proceed further due to a permissions issue, then try the following as a solution:

  • Open a windows command prompt as an administrator. You can do this by selecting the command prompt icon in the start menu and accessing the context menu.
  • Then in the administrative command prompt run:
adb root

Change directory into sqlite.myrentsqlite.

cd sqlite.myrentsqlite

Check its contents:

ls -l

drwxrwx--x u0_a80   u0_a80            2016-07-15 10:57 cache
drwxrwx--x u0_a80   u0_a80            2016-07-15 10:57 code_cache
drwxrwx--x u0_a80   u0_a80            2016-07-15 10:57 databases
drwx------ u0_a80   u0_a80            2016-07-15 10:57 files

Change into databases folder and check the contents:

cd databases
ls -l

Here is typical output:

...
-rw-rw---- u0_a80   u0_a80      20480 2016-07-15 10:58 residences.db
-rw------- u0_a80   u0_a80      12824 2016-07-15 10:58 residences.db-journal

The database file residences.db is present. This name was assigned in the DbHelper class. We shall now inspect its contents. Run the following commands:

sqlite3 residences.db

The response should be something like the following:

SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite>

Check what tables are present in the database:

.tables

In our case the response should be as follows:

android_metadata  tableResidences 
sqlite>

We have added one Residence object record to the database. This can now be verified:

sqlite> select * from tableResidences;
efb14e04-0820-4cfc-9520-1f4c391d0974|52.253456,-7.187162

To exit sqlite run this command:

.exit

To exit adb run a similar command but without the period:

exit

Use the above technique to regularly check the database table(s) as you progressively develop the app.

Running .exit and exit in succession will return you to the terminal command prompt.

Official documentation:

Select a Residence

In this step, we provide the additional code to select a single residence from the database.

Layout (xml)

Add a Delete Residence button to the layout:

  <Button
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:text="Select Residence"
      android:id="@+id/selectResidence"
      android:layout_alignBottom="@+id/addResidence"
      android:layout_alignParentEnd="true"
      android:layout_marginEnd="27dp"/>

DbHelper

Add this method to DbHelper.java:

  public Residence selectResidence(UUID resId) {
    Residence residence;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = null;

    try {
      residence = new Residence();

      cursor = db.rawQuery("SELECT * FROM tableResidences WHERE id = ?", new String[]{resId.toString() + ""});

      if (cursor.getCount() > 0) {
        int columnIndex = 0;
        cursor.moveToFirst();
        residence.id = UUID.fromString(cursor.getString(columnIndex++));
        residence.geolocation = cursor.getString(columnIndex++);
      }
    } finally {
      cursor.close();
    }
    return residence;
  }

These import statements are required:

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;

MyRent (activity)

The following additional code snippets are required:

  • A Button field.
  • Binding of the button resource in the R file to the new button field.
  • Setting a listener on the button.
  • Intercepting a button click.
  • Implementing a method selectResidence that in turn invokes DbHelper.deleteResidence.

Here are the code snippets:

  private Button selectResidence;
    selectResidence = (Button) findViewById(R.id.selectResidence);
    selectResidence.setOnClickListener(this);

      case R.id.selectResidence:
        selectResidence();
        break;
  /**
   * This method demonstrates how to select a Residence record, identified by
   * its primary key, the UUID field.
   * Invoking addResidence() writes a Residence record to the database.
   * Additionally, it initializes this.residence field.
   * The id of this.residence is then used as a parameter in DbHelper.selectResidence.
   */
  public void selectResidence()
  {
    addResidence();
    UUID uuid = residence.id;
    Residence selectedResidence = app.dbHelper.selectResidence(uuid);
    if (residence != null && residence.id.toString().equals(selectedResidence.id.toString()))
    {
      Toast.makeText(this, "Residence record selected(id: " + residence.id, Toast.LENGTH_LONG).show();
    }
    else
    {
      Toast.makeText(this, "Failed to select Residence record", Toast.LENGTH_LONG).show();
    }
  }

Toast and UUID imports are required:

import android.widget.Toast;
import java.util.UUID;

Delete a Residence

In this step, we provide the additional code to delete a single residence from the database.

Layout (xml)

Add a Delete Residence button to the layout:

  <Button
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:text="Delete Residence"
      android:id="@+id/deleteResidence"
      android:layout_below="@+id/addResidence"
      android:layout_alignParentStart="true"
      android:layout_marginTop="42dp"/>

DbHelper

Add this method to DbHelper.java:

    public void deleteResidence(Residence residence) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            db.delete("tableResidences", "id" + "=?", new String[]{residence.id.toString() + ""});
        } catch (Exception e) {
            Log.d(TAG, "delete residence failure: " + e.getMessage());
        }
    }

MyRent (activity)

The following additional items are required:

  • A Button field.
  • Binding of the button resource in the R file to the new button field.
  • Setting a listener on the button.
  • Intercepting a button click.
  • Implementing a method deleteResidence that in turn invokes DbHelper.deleteResidence.

Here are the code snippets:

  private Button deleteResidence;
    deleteResidence = (Button) findViewById(R.id.deleteResidence);
    deleteResidence.setOnClickListener(this);
@Override
  public void onClick(View v)
  {
    switch (v.getId())
    {

      ...
      ...

      case R.id.deleteResidence:
        deleteResidence();
        break;

    }

  }
  public void deleteResidence()
  {
    if (residence == null)
    {
      addResidence();
    }
    else {
      Residence res = app.dbHelper.selectResidence(residence.id);
      app.dbHelper.deleteResidence(res);
    }
  }

Debug into the app and using adb or otherwise verify that this feature works correctly.

List all residences

In this step we shall select the entire tableResidences record set and store in a List array.

Layout (xml)

  <Button
      android:id="@+id/selectAllResidences"
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:layout_alignBottom="@+id/deleteResidence"
      android:layout_alignEnd="@+id/selectResidence"
      android:text="Select All"/>

DbHelper

  /**
   * Query database and select entire tableResidences.
   * 
   * @return A list of Residence object records
   */
  public List<Residence> selectAllResidences() {
    List<Residence> residences = new ArrayList<Residence>();
    String query = "SELECT * FROM " + "tableResidences";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);
    if (cursor.moveToFirst()) {
      int columnIndex = 0;
      do {
        Residence residence = new Residence();
        residence.id = UUID.fromString(cursor.getString(columnIndex++));
        residence.geolocation = cursor.getString(columnIndex++);

        columnIndex = 0;

        residences.add(residence);
      } while (cursor.moveToNext());
    }
    cursor.close();
    return residences;
  }

MyRent (activity)

  private Button selectAllResidences;
    selectAllResidences = (Button) findViewById(R.id.selectAllResidences);
    selectAllResidences.setOnClickListener(this);
      case R.id.selectAllResidences:
        selectAllResidences();
        break;
  public void selectAllResidences() {
    List<Residence> residences = app.dbHelper.selectAllResidences();
    Toast.makeText(this, "Retrieved residence list containing  " + residences.size() + " records", Toast.LENGTH_LONG).show();
  }

Import the List class:

import java.util.List;

Debug into the app to verify that this feature works correctly.

Delete all

In this step we shall delete the entire tableResidences record set.

Layout (xml)


  <Button
      android:id="@+id/deleteAllResidences"
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:layout_marginTop="49dp"
      android:text="Delete All"
      android:layout_below="@+id/deleteResidence"
      android:layout_alignParentStart="true"/>

DbHelper

  /**
   * Delete all records
   */
  public void deleteAllResidences() {
    SQLiteDatabase db = this.getWritableDatabase();
    try {
      db.execSQL("delete from tableResidences");
    } catch (Exception e) {
      Log.d(TAG, "delete residences failure: " + e.getMessage());
    }
  }

Add a method to obtain the number of rows in the database:

  /**
   * Queries the database for the number of records.
   *
   * @return The number of records in the dataabase.
   */
  public long getCount() {
    SQLiteDatabase db = this.getReadableDatabase();
    long numberRecords  = DatabaseUtils.queryNumEntries(db, TABLE_RESIDENCES);
    db.close();
    return numberRecords;
  }

Required import:

import android.database.DatabaseUtils;

MyRent (activity)

  private Button deleteAllResidences;
    deleteAllResidences = (Button) findViewById(R.id.deleteAllResidences);
    deleteAllResidences.setOnClickListener(this);
      case R.id.deleteAllResidences:
        deleteAllResidences();
        break;
  /**
   * Delete all records.
   * Count the number of rows in database following deletion -should be zero.
   * Provide user feed back in a toast.
   */
  public void deleteAllResidences() {
    app.dbHelper.deleteAllResidences();
    Toast.makeText(this, "Number of records in database " + app.dbHelper.getCount(), Toast.LENGTH_LONG).show();

  }

Residence model complete

Here will shall add the remaining fields to the Residence model and refactor the database helper appropriately.

Model Residence

package sqlite.myrentsqlite.models;

import java.util.Date;
import java.util.UUID;

public class Residence
{
  public UUID id;
  public String geolocation;
  public Date date;
  public boolean rented;
  public String tenant;
  public double zoom;//zoom level of accompanying map
  public String photo;


  public Residence()
  {
    id = UUID.randomUUID();
    geolocation = "52.253456,-7.187162";
    date = new Date();
    rented = false;
    tenant = ": none presently";
    zoom = 16.0;
    photo = "photo";
  }

}

DbHelper (Complete)

Note in particular how the Date date, boolean rented and double zoom fields are transformed as they are inserted to and read from the database.

package sqlite.myrentsqlite.app;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import sqlite.myrentsqlite.models.Residence;

public class DbHelper extends SQLiteOpenHelper
{
  static final String TAG = "DbHelper";
  static final String DATABASE_NAME = "residences.db";
  static final int DATABASE_VERSION = 1;
  static final String TABLE_RESIDENCES = "tableResidences";

  static final String PRIMARY_KEY = "id";
  static final String GEOLOCATION = "geolocation";
  static final String DATE = "date";
  static final String RENTED = "rented";
  static final String TENANT = "tenant";
  static final String ZOOM = "zoom";
  static final String PHOTO = "photo";

  Context context;

  public DbHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    this.context = context;
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    String createTable =
        "CREATE TABLE tableResidences " +
            "(id text primary key, " +
            "geolocation text," +
            "date text," +
            "rented text," +
            "tenant text," +
            "zoom text," +
            "photo text)";

    db.execSQL(createTable);
    Log.d(TAG, "DbHelper.onCreated: " + createTable);
  }

  /**
   * @param residence Reference to Residence object to be added to database
   */
  public void addResidence(Residence residence) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(PRIMARY_KEY, residence.id.toString());
    values.put(GEOLOCATION, residence.geolocation);

    values.put(DATE, String.valueOf(residence.date.getTime()));
    values.put(RENTED, residence.rented == true ? "yes" : "no");
    values.put(TENANT, residence.tenant);
    values.put(ZOOM, Double.toString(residence.zoom));
    values.put(PHOTO, residence.photo);

    // Insert record
    db.insert(TABLE_RESIDENCES, null, values);
    db.close();
  }

  public Residence selectResidence(UUID resId) {
    Residence residence;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = null;

    try {
      residence = new Residence();

      cursor = db.rawQuery("SELECT * FROM tableResidences WHERE id = ?", new String[]{resId.toString() + ""});

      if (cursor.getCount() > 0) {
        int columnIndex = 0;
        cursor.moveToFirst();
        residence.id = UUID.fromString(cursor.getString(columnIndex++));
        residence.geolocation = cursor.getString(columnIndex++);
        residence.date = new Date(Long.parseLong(cursor.getString(columnIndex++)));
        residence.rented = cursor.getString(columnIndex++) == "yes" ? true : false;
        residence.tenant = cursor.getString(columnIndex++);
        residence.zoom = Double.parseDouble(cursor.getString(columnIndex++));
        residence.photo = cursor.getString(columnIndex++);
      }
    }
    finally {
      cursor.close();
    }
    return residence;
  }

  public void deleteResidence(Residence residence) {
    SQLiteDatabase db = this.getWritableDatabase();
    try {
      db.delete("tableResidences", "id" + "=?", new String[]{residence.id.toString() + ""});
    }
    catch (Exception e) {
      Log.d(TAG, "delete residence failure: " + e.getMessage());
    }
  }

  /**
   * Query database and select entire tableResidences.
   *
   * @return A list of Residence object records
   */
  public List<Residence> selectAllResidences() {
    List<Residence> residences = new ArrayList<Residence>();
    String query = "SELECT * FROM " + "tableResidences";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);
    if (cursor.moveToFirst()) {
      int columnIndex = 0;
      do {
        Residence residence = new Residence();
        residence.id = UUID.fromString(cursor.getString(columnIndex++));
        residence.geolocation = cursor.getString(columnIndex++);
        residence.date = new Date(Long.parseLong(cursor.getString(columnIndex++)));
        residence.rented = cursor.getString(columnIndex++) == "yes" ? true : false;
        residence.tenant = cursor.getString(columnIndex++);
        residence.zoom = Double.parseDouble(cursor.getString(columnIndex++));
        residence.photo = cursor.getString(columnIndex++);
        columnIndex = 0;

        residences.add(residence);
      } while (cursor.moveToNext());
    }
    cursor.close();
    return residences;
  }

  /**
   * Delete all records
   */
  public void deleteAllResidences() {
    SQLiteDatabase db = this.getWritableDatabase();
    try {
      db.execSQL("delete from tableResidences");
    } catch (Exception e) {
      Log.d(TAG, "delete residences failure: " + e.getMessage());
    }
  }

  /**
   * Queries the database for the number of records.
   *
   * @return The number of records in the dataabase.
   */
  public long getCount() {
    SQLiteDatabase db = this.getReadableDatabase();
    long numberRecords  = DatabaseUtils.queryNumEntries(db, TABLE_RESIDENCES);
    db.close();
    return numberRecords;
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("drop table if exists " + TABLE_RESIDENCES);
    Log.d(TAG, "onUpdated");
    onCreate(db);
  }
}

Update

In this the final step we shall update a tableResidences record - a Residence object.

Layout (xml)

  <Button
      android:id="@+id/updateResidence"
      android:layout_width="wrap_content"
      android:layout_height="wrap_content"
      android:text="Update"
      android:layout_alignTop="@+id/deleteAllResidences"
      android:layout_alignEnd="@+id/selectAllResidences"/>

DbHelper

  /**
   * Update an existing Residence record.
   * All fields except record id updated.
   * 
   * @param residence The Residence record being updated.     
   */
  public void updateResidence(Residence residence) {
    SQLiteDatabase db = this.getWritableDatabase();
    try {
      ContentValues values = new ContentValues();
      values.put(GEOLOCATION, residence.geolocation);
      values.put(DATE, String.valueOf(residence.date.getTime()));
      values.put(RENTED, residence.rented == true ? "yes" : "no");
      values.put(TENANT, residence.tenant);
      values.put(ZOOM, Double.toString(residence.zoom));
      values.put(PHOTO, residence.photo);
      db.update("tableResidences", values, "id" + "=?",  new String[]{residence.id.toString() + ""});
    } catch (Exception e) {
      Log.d(TAG, "update residences failure: " + e.getMessage());
    }
  }

MyRent (activity)


  private Button updateResidence;
    updateResidence = (Button) findViewById(R.id.updateResidence);
    updateResidence.setOnClickListener(this);
      case R.id.updateResidence:
        updateResidence();
        break;
  /**
   * Update a residence record.
   * Create and insert a test record.
   * Make some changes to its fields and update its copy in the database.
   * Verify and provide toast feedback.
   */
  public void updateResidence() {
    addResidence(); // This initializes the instance variable Residence residence
    Residence res = app.dbHelper.selectResidence(residence.id);
    // Makes some distinguishing changes to res fields
    res.tenant = "Barney Gumble";
    res.rented = true;
    res.zoom = 20;

    app.dbHelper.updateResidence(res);

    // Read the updated rrow and verify it's correct.
    Residence res2 = app.dbHelper.selectResidence(res.id);
    boolean b = res.zoom == res2.zoom;
    if (b == true) {
      Toast.makeText(this, "Update succeeded", Toast.LENGTH_LONG).show();
    }
    else {
      Toast.makeText(this, "Update failed", Toast.LENGTH_LONG).show();
    }
  }

A completed version of the application is available to download from GitHub: MyRentSQLite

Exercises

(1) Use this lab to integrate an SQLite database in MyRent app.

(2 )Add a feature to DbHelper to insert a list of Residence records.

Presently a method exists to insert a single record. Extend the:

  • UI (add a button)
  • MyRent activity (add an event handler)
  • DbHelper (add residences method).

Android Debug Bridge (adb)

adb is a commandline tool to view and manipulate SQlite databases generated within an Android app.

Here is the link for official Android documentation: Android Debug Bridge

In this step we will:

  • very briefly describe how modify the path environment variable on your computer (Mac & Windows) by adding the path to the Android Debug Bridge utility (adb).

  • outline how to manually delete the database data using adb

Add Android Debug Bridge to the PATH variable

  • On the Mac (OSX)

The Android Debug Bridge (adb) is located in the platform-tools folder in the ADT bundle.

It is necessary to insert the path to adb on the PATH environment variable.

The path to this utility might be something like the following (amend appropriately for your particular environment):

$HOME/android-dev/adt-bundle-mac-x86_64-20130729/sdk/platform-tools

In OSX this can be added to the PATH variable by editing the .bash_profile file which is located in the home folder. Quoting from, for example, OSX Mountain Lion support page :

In Windows 10, a typical path to adb.exe is as follows:

C:\Users\User\AppData\Local\Android\sdk\platform-tools

The home folder stores your documents, preference settings, and other information. A home folder is created for each user of your computer when his or her account is set up. If you are the only user of your computer, there is only one home folder. The home folder isn’t actually named “Home”; its name is the same as the account name specified in your user account. Your home folder appears in the sidebar of Finder windows.

See also OSX El Capitan: home folder.

Open a terminal window. Check that you are in the home folder. Use Sublime Text editor to open .bash_profile*

The first line of the modified path could be this:

PATH=\$PATH:/\$HOME/android-dev/adt-bundle-mac-x86_64-20130729/sdk/platform-tools:\

  • On Windows

On Windows you may edit the path by accessing

System Properties > Advanced > Environment Variables

Using adb

In a terminal window execute

adb shell

This will change the command prompt to root@generic:/ ... .

Change directory:

cd data/data

Type *ls sqlite.myrentsqlite.

You should see something like that shown in Figure 1 :

Figure 1: Using *adb*

To remove the database execute

rm -rf sqlite.myrentsqlite/databases

It's important to note that the name of the folder containg the database, sqlite.myrentsqlite , derives from the package name in the AndroidManifest.xml file and that the name of the database, residences.db, is that which we have assigned in DbHelper.java. The table, tableResidences, will also have been assigned in DbHelper.

SQLite Browser

A third-party SQLite browser provides an alternative approach to adb to view the database content.

Figure 1: SQLite browser

Detailed information on the installation and use of the browser is contained in the stackoverflow article View contents of database file in Android Studio, summarized in the the following screenshots:

Figure 2: Viewing Android Studio generated database Figure 3: Viewing Android Studio generated database Figure 4: Sample tableResidences records